
const express = require('express');
const router = express.Router();
const pool = require('../config/bd');
const { v4: uuidv4 } = require('uuid'); // 导入后使用
const { validate: isValidUUID } = require('uuid');

//  GET /records
//  查询账本记录（支持分页、筛选）
//  参数：
//    userId (必填)
//    familyId (可选)
//    startTime/endTime (可选时间范围)
//    category (可选分类)
//    page=1&pageSize=10

router.get('/records', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    const { userId, familyId, startTime, endTime } = req.query;

    // 强化参数校验（网页6的UUID验证逻辑）
    if (!isValidUUID(userId) || (familyId && !isValidUUID(familyId))) {
      return res.status(400).json({
        code: 400,
        message: 'ID格式异常'
      });
    }

    // 获取用户家庭归属
    const [userInfo] = await connection.query(
      `SELECT family_id FROM user WHERE id = ?`,
      [userId]
    );

    if (!userInfo.length) {
      return res.status(404).json({
        code: 404,
        message: '用户不存在'
      });
    }

    // 强制家庭过滤（网页8的WHERE子句应用）
    const effectiveFamilyId = familyId || userInfo[0].family_id;
    if (effectiveFamilyId !== userInfo[0].family_id) {
      return res.status(403).json({
        code: 403,
        message: '无权限访问该家庭数据'
      });
    }

    // 重构查询语句（网页6的JOIN优化）
    let sql = `
      SELECT 
        r.record_id, r.amount, r.type,
        DATE_FORMAT(r.event_time, '%Y-%m-%d') AS event_time,
        r.description, r.payment_method, r.category, r.remark,
        u.nickname AS operator_name
      FROM ledger_record r
      JOIN user u ON r.user_id = u.id
      WHERE r.family_id = ?`;
    const params = [effectiveFamilyId];

    // 时间过滤增强（网页8的BETWEEN优化）
    if (startTime && endTime) {
      sql += ' AND r.event_time BETWEEN ? AND ?';
      params.push(startTime, endTime);
    }

    // 执行参数化查询（网页6的安全建议）
    const [rows] = await connection.execute(sql, params);

    res.json({
      code: 200,
      success: true,
      message: '数据查询成功',
      data: rows
    });

  } catch (err) {
    console.error('查询失败:', err);
    res.status(500).json({
      code: 500,
      message: '服务器内部错误'
    });
  } finally {
    connection.release();
  }
});

//   更新指定记录（需返回更新后的完整数据）
router.put('/records/:id', async (req, res) => {
  try {
    const recordId = req.params.id;
    const updateFields = req.body;

    // 验证可更新字段
    const allowedFields = ['amount', 'type', 'event_time',
      'description', 'payment_method', 'category', 'remark'];
    const validUpdate = {};

    Object.keys(updateFields).forEach(key => {
      if (allowedFields.includes(key)) {
        validUpdate[key] = updateFields[key];
      }
    });

    // 构建更新语句
    const setClause = Object.keys(validUpdate)
      .map(key => `${key} = ?`).join(', ');
    const values = Object.values(validUpdate);
    values.push(recordId);

    // 执行更新
    const [updateResult] = await pool.execute(
      `UPDATE ledger_record 
       SET ${setClause}
       WHERE record_id = ?`,
      values
    );

    if (updateResult.affectedRows === 0) {
      return res.status(404).json({
        code: 404,
        message: '记录不存在'
      });
    }

    // 查询更新后的数据
    const [updatedRow] = await pool.execute(
      `SELECT record_id, amount, type, event_time, 
              description, payment_method, category, remark
       FROM ledger_record 
       WHERE record_id = ?`,
      [recordId]
    );

    res.json({
      code: 200,
      data: updatedRow[0],
      message: '更新成功'
    });

  } catch (err) {
    console.error('更新失败:', err);
    res.status(500).json({
      code: 500,
      message: '更新失败: ' + err.message
    });
  }
});

//  * 创建新账本记录（返回完整插入数据）
router.post('/records', async (req, res) => {
  try {
    const newRecord = {
      // 必填字段
      user_id: req.body.userId,
      amount: req.body.amount,
      event_time: req.body.eventTime || new Date(),
      description: req.body.description,
      //可选
      family_id: req.body.familyId,  // 显式设置null
      type: req.body.type || '支出',
      payment_method: req.body.paymentMethod || null,
      category: req.body.category || null,
      remark: req.body.remark || null
    };


    // 生成UUID（参考网页[4]的UUID生成思路）
    const recordId = uuidv4();

    // 执行插入操作（参数化查询防注入）
    const [insertResult] = await pool.execute(
      `INSERT INTO ledger_record 
       (record_id, user_id, family_id, amount, type, 
        event_time, description, payment_method, category, remark)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        recordId,
        newRecord.user_id,
        newRecord.family_id,
        newRecord.amount,
        newRecord.type || '支出',
        newRecord.event_time,
        newRecord.description,
        newRecord.payment_method || null,
        newRecord.category || null,
        newRecord.remark || null
      ]
    );

    // 获取完整插入数据（参考网页[8]的返回思路）
    const [createdRecord] = await pool.execute(
      `SELECT record_id, amount, type, event_time, 
              description, payment_method, category, remark
       FROM ledger_record 
       WHERE record_id = ?`,
      [recordId]
    );

    res.status(201).json({
      code: 201,
      data: createdRecord[0],
      success: true,
      message: '数据创建成功!'
    });

  } catch (err) {
    console.error('创建失败:', err);
    res.status(500).json({
      code: 500,
      message: '创建失败: ' + err.message
    });
  }
});

module.exports = router;